﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient;

namespace cls.u_Models
{
    public partial class ORAFileUpload : System.Web.UI.Page
    {
        static String AppName = "";
        static String OrgType = "";
        static String OrgCode = "";
        static String ReturnUrl = "";
        static String UserName = "";
        static String ExcelFilePath = "";
        static DateTime DateTimeNow;

        protected void Page_Load(object sender, EventArgs e)
        {
            UserName = User.Identity.Name;
            AppName = Request.QueryString["App"];
            OrgType = Request.QueryString["OrgType"];
            OrgCode = Request.QueryString["OrgCode"];

            if (AppName == null || AppName.Equals(""))
            {
                Response.Redirect("../Default.aspx");
            }

            Label_AppName.Text = "";

            if (AppName.Equals("tPluPreElimination"))
            {
                Label_AppName.Text = "您要上传的是预淘汰商品明细Excel表，请确保要上传的文件没有被其他程序打开！";
                ReturnUrl = "tPluPreElimination.aspx";
            }

            //显示用户提示：
            Label_AppName.ForeColor = Color.Red;
            //Label_AppName.Text = "程序出了一点小问题......先不要上传！";

            if (UserName == null || UserName.Equals(""))
            {
                Response.Redirect("../Default.aspx");
            }
            else if (UserName.Trim().Equals("徐津煜"))
            {
                Button_Test.Visible = true;
            }
            else
            {
                Button_Test.Visible = false;
            }

            if (!Page.IsPostBack)
            {
                Button_FileUpload.Enabled = true;
            }
        }

        protected void Button_FileUpload_Click(object sender, EventArgs e)
        {
            //Label_DebugMsg.Text = "";
            //Label_DebugMsg.Text = "======== 以下是调试信息，用于跟踪程序错误，不是“乱码”。 ========<br />";

            Label_UploadStatus.Text = "";

            //点击按钮的时间
            DateTimeNow = DateTime.Now;

            if (FileUpload_ExcelFile.HasFile)
            {
                Label_DebugMsg.Text = "";

                //允许的文件类型


                SaveExcelFileToServer(GetExcelFilePathName(), UserName);

                //加入AppName判定
                //ExcelImportToSQLDB(GetExcelFilePathName(), AppName);
                ExcelImportToORADB(GetExcelFilePathName(), AppName);

                //状态显示
                Label_UploadStatus.ForeColor = Color.Red;
                //Label_UploadStatus.Text = "这里显示的是简体中文的汉字，不是“乱码”！<br />";
                //应该是成功了......
                Label_UploadStatus.Text = "上传成功！<br />";
            }
            else
            {
                Label_UploadStatus.Text = "请选择正确的文件。";
            }
        }

        protected void SaveExcelFileToServer(String ExcelFilePathName, String UserName)
        {
            FileUpload_ExcelFile.PostedFile.SaveAs(ExcelFilePathName);
            RecLastInsDatetime(AppName, OrgCode, ExcelFilePathName, UserName, DateTimeNow);
        }

        protected String GetExcelFilePathName()
        {
            String ExcelFilePathName = "";

            ExcelFilePath = MapPath("~/u_ExcelFiles/");
            if (FileUpload_ExcelFile.HasFile)
            {
                String ExcelFileName = Path.GetFileNameWithoutExtension(FileUpload_ExcelFile.FileName) + "_" + DateTimeNow.ToString("yyyy-MM-dd_HHmmss", new CultureInfo("zh-CN")) + "_" + UserName + Path.GetExtension(FileUpload_ExcelFile.FileName);

                ExcelFilePathName = ExcelFilePath + ExcelFileName;
            }

            return ExcelFilePathName;
        }

        protected void ExcelImportToORADB(String ExcelFIlePathName, String AppName)
        {
            //用存储过程实现太麻烦了，还是直接用语句吧......
            //http://blog.csdn.net/andylaufzf/article/details/2143716
            //http://www.cnblogs.com/lhking/archive/2009/06/08/1499002.html

            String SheetName = "";
            String DeleteTableName = "";
            String InsertTableName = "";
            String DeleteOraString = "";
            String InsertOraString = "";

            ArrayList TableColumnNames = new ArrayList();

            String ColumnNames = "";

            //注意
            //AppName判定不同的Sql Table、SheetName
            //CA1062：验证公共方法的参数
            //http://msdn.microsoft.com/query/dev12.query?appId=Dev12IDEF1&l=ZH-CN&k=k(CA1062);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.0)&rd=true
            //注意
            //是Oracle 11.2.0的表
            if (AppName.Equals("tPluPreElimination"))
            {                
                TableColumnNames = GetDbTableColumnName("tPluPreElimination");
                
                SheetName = "tPluPreElimination$";
                DeleteTableName = "tPluPreElimination";
                InsertTableName = "tPluPreElimination";
            }

            //注意
            //AppName判定不同的DataSet
            DataSet ExcelDataSet = GetExcelDataSet(ExcelFIlePathName, SheetName, AppName);

            for (int i = 0; i < TableColumnNames.Count; i++)
            {
                //Oracle的列名无需[]
                //ColumnNames += "[" + TableColumnNames[i].ToString() + "]" + ",";
                ColumnNames += TableColumnNames[i].ToString() + ",";
            }

            //定义最后一个,的下标。
            //注意,是Char不是String，所以用','而不是","
            int LastCharIndex = ColumnNames.LastIndexOf(',');

            //移除最后一个","
            ColumnNames = ColumnNames.Remove(LastCharIndex);

            //调试信息
            //显示所有列名，加入分隔符，用于INSERT INTO语句            
            //Label_DebugMsg.Text += LastCharIndex + "<br />";
            //Label_DebugMsg.Text += ColumnNames + "<br />";            

            //SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            //SqlConn.Open();

            //构建Oracle连接
            //ConnectionString格式参考：
            //http://www.connectionstrings.com/ace-oledb-12-0/
            OracleConnection OraConnection = new OracleConnection("Data Source=hsczbcmp;User ID=hscmp;Password=Cmp_Data;Integrated Security=false;Persist Security Info=false");
            
            //打开Oracle连接
            OraConnection.Open();

            //Insert之前，要先把历史记录Delete掉。
            OracleCommand DeleteCommand = new OracleCommand();
            OracleCommand InsertCommand = new OracleCommand();
            DeleteCommand.Connection = OraConnection;
            DeleteCommand.CommandType = CommandType.Text;
            InsertCommand.Connection = OraConnection;
            InsertCommand.CommandType = CommandType.Text;

            //Insert之前，要先把历史记录Delete掉。
            DeleteOraString = "DELETE FROM " + DeleteTableName;            
            DeleteCommand.CommandText = DeleteOraString;            
            DeleteCommand.ExecuteNonQuery();

            //构建INSERT INTO语句中的VALUES
            for (int h = 0; h < ExcelDataSet.Tables[0].Rows.Count; h++)
            {
                //GUID作为Id列
                //Oracle不需要GUID列
                //String ColumeValues = "'" + System.Guid.NewGuid().ToString() + "'" + ",";
                String ColumeValues = "";

                //Saleplan的特殊处理
                //此处不需要进行处理
                //
                //if (AppName.Equals("Saleplan"))
                //{
                //    //ColumeValues += "'" + OrgType + "','" + OrgCode + "',";
                //    ColumeValues += "'" + OrgType + "',";
                //}

                //根据列名得到值
                for (int k = 0; k < ExcelDataSet.Tables[0].Columns.Count; k++)
                {
                    //SQLServer存在问题
                    //从数据类型 varchar 转换为 numeric 时出错。
                    //u_cls_Issuseplu_Table表的InvCount(decimal(18,2))和InvCount(decimal(18,2))
                    //除了改成nvarchar，还有什么方法......
                    ColumeValues += "'" + ExcelDataSet.Tables[0].Rows[h][k].ToString() + "'" + ",";
                }

                //移除VALUES中的最后一个","
                int n = ColumeValues.LastIndexOf(',');
                ColumeValues = ColumeValues.Remove(n);

                if (AppName.Equals("tPluPreElimination"))
                {                    
                    //DeleteCommand
                    //
                    //Oracle的列类型DATE和VS的DateTme不特么的兼容！
                    //不要这个DateTimeNow了。
                    //InsertOraString = "INSERT INTO " + InsertTableName + " (" + ColumnNames + ") VALUES (" + ColumeValues + ",'')";
                    //卧槽，是特么的我弄错了......
                    //InsertOraString = "INSERT INTO tPluPreElimination (" + ColumnNames + ") VALUES (" + ColumeValues + "," +"'" + DateTimeNow + "'" + ")";
                    //Oracle还是需要TO_DATE()函数                    
                    InsertOraString = "INSERT INTO " + InsertTableName + " (" + ColumnNames + ") VALUES (" + ColumeValues + ",'" +UserName+"',"+ "TO_DATE('" + DateTimeNow + "','yyyy-mm-dd hh24:mi:ss')" + ")";
                    
                }
                else if (AppName.Equals("tPluNew"))
                {
                    InsertOraString = "INSERT INTO " + InsertTableName + " (" + ColumnNames + ") VALUES (" + ColumeValues + ",'" + "TO_DATE(" + DateTimeNow + ")" + "','" + UserName + "')";
                }
                else if (AppName.Equals("Saleplan"))
                {
                    InsertOraString = "INSERT INTO [dbo].[u_cls_Saleplan_Table] ([id],[OrgType],[PlanYearMonth],[OrgCode],[OrgName],[OrgPlan],[WeekOne_ShopPlan],[WeekTwo_ShopPlan],[WeekThree_ShopPlan],[WeekFour_ShopPlan],[WeekDateRemark],[ImportDatetime],[ImportUserName],[InsDatetime],[InsUserName]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','')";

                    //InsertSqlString = "INSERT INTO [dbo].[u_cls_Saleplan_Table] ([id],[OrgType],[PlanYearMonth],[OrgCode],[OrgName],[OrgPlan],[WeekOne_ShopPlan],[WeekTwo_ShopPlan],[WeekThree_ShopPlan],[WeekFour_ShopPlan],[WeekDateRemark],[InsDatetime],[InsUserName],[ImportDatetime],[ImportUserName]) VALUES (" + ColumeValues + ",'" + DateTimeNow + "','" + UserName + "','','')";
                }

                ///调试信息
                //Label_DebugMsg.Text += "ColumeValues[" + ColumeValues + "]:<br />" ;

                //调试信息，显示INSERT INTO语句                
                Label_DebugMsg.Text += "InsertSqlString[" + h + "]:<br />" + InsertOraString + "<br />";
                
                InsertCommand.CommandText = InsertOraString;                

                try
                {                    
                    InsertCommand.ExecuteNonQuery();

                    Label_UploadStatus.Text = "上传成功！<br />";
                }
                catch (Exception e)
                {
                    Label_UploadStatus.Text += "上传失败，InsertCommand.ExecuteNonQuery()错误！<br />";
                    Label_UploadStatus.Text += "错误信息：" + e.Message + "<br />";
                    //Label_DebugMsg.Text += e.Message + "<br />";
                    //Label_DebugMsg.Text += "------------------------------------------------------------------";
                    //Label_DebugMsg.Text += e.StackTrace + "<br />";
                    //Label_DebugMsg.Text += "------------------------------------------------------------------";
                    
                    //写入日志
                    //WriteLog("e.Message:<br />" + e.Message + "<br />" + "e.StackTrace:<br />" + e.StackTrace + "<br />");
                }
                //finally
                //{

                //}
            }

            InsertCommand.Dispose();
            OraConnection.Close();
        }

        public DataSet GetExcelDataSet(String ExcelFilePathName, String SheetName, String AppName)
        {
            DataSet ExcelDataSet = new DataSet();

            //备忘：
            //2007 Office system 驱动程序：数据连接组件（32位）
            //http://www.microsoft.com/zh-cn/download/details.aspx?id=23734

            //这里使用：
            //Microsoft Access 2010 数据库引擎可再发行程序包（64位）
            //http://www.microsoft.com/zh-CN/download/details.aspx?id=13255
            //如果要连接到 Microsoft Office Excel 数据，请根据 Excel 文件类型添加相应的 OLEDB 连接字符串扩展属性：
            //
            //文件类型（扩展名）                                 扩展属性
            //---------------------------------------------------------------------------------------------
            //Excel 97-2003 工作簿（.xls）                      “Excel 8.0”
            //Excel 2007-2010 工作簿（.xlsx）                   “Excel 12.0 Xml”
            //启用宏的 Excel 2007-2010 工作簿（.xlsm）           “Excel 12.0 宏”
            //Excel 2007-2010 非 XML 二进制工作簿（.xlsb）      “Excel 12.0”

            //关于HDR和IMEX：
            //http://blog.sina.com.cn/s/blog_6e001be701016yi8.html
            //HDR 表示第一行是否是标题行
            //若为 YES，则第一行是标题行（即列名称），不是数据；
            //若为 NO，则第一行不是标题行，跟后面的行一样，是数据。
            //IMEX 表示混合数据类型时如何处理。
            //若为 0，则为输出模式，此情况下只能用作写入 Excel；
            //若为 1，则为输入模式，此情况下只能用作读取 Excel，并且始终将 Excel 数据作为文本类型读取；
            //若为 2，则为连接模式，此情况下既可用作写入、也可用作读取。

            //ConnectionString格式：
            //http://www.connectionstrings.com/ace-oledb-12-0/

            String ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePathName.Replace("\\", "\\\\") + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes\"";

            OleDbConnection OleDBConn = new OleDbConnection(ConString);

            //问题：
            //[OleDbException (0x80004005): Microsoft Access 数据库引擎无法打开文件“”或向其写入数据。它已经被其他用户以独占方式打开，或者您没有查看和写入其数据的权限。]
            //解决方法：上传至非系统盘目录，然后读取。
            OleDBConn.Open();

            OleDbDataAdapter OleDbDA = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "]", OleDBConn);

            //OleDbDA.Fill(ExcelDataSet, "XLData");
            OleDbDA.Fill(ExcelDataSet);

            OleDbDA.Dispose();
            OleDBConn.Close();

            return ExcelDataSet;
        }

        public ArrayList GetDbTableColumnName(string TableName)
        {
            ArrayList AL_ColumnNames = new ArrayList();

            //注意
            //Oracle连接
            //参考
            //http://msdn.microsoft.com/zh-cn/library/system.data.oracleclient.oracleconnection.connectionstring(v=vs.110).aspx
            //http://www.oracle.com/technetwork/topics/dotnet/whatsnew/index.html
            //http://www.oracle.com/technetwork/topics/dotnet/whatsnew/vs2012welcome-1835382.html
            //
            OracleConnection OraConnection = new OracleConnection("Data Source=hsczbcmp;User ID=hscmp;Password=Cmp_Data;Integrated Security=false;Persist Security Info=false");
            //
            //Provider=SQLDB会导致Keyword not supported: 'provider'.
            //Integrated Security=true,会导致ORA-01017: invalid username/password; logon denied
            //
            //OracleConnection OraConnection = new OracleConnection("Data Source=hsczbcmp;User ID=hscmp;Password=Cmp_Data;Integrated Security=true");

            OraConnection.Open();            

            //Oracle中的TableName需要转换为大写
            //因为ALL_TAB_COLUMNS中的COLUMN_NAME列值是大写
            TableName = TableName.ToUpper();

            OracleCommand OraCommand = new OracleCommand("SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = 'HSCMP' AND TABLE_NAME = '" + TableName  +"'", OraConnection);

            OracleDataReader OraDr = OraCommand.ExecuteReader();
            while (OraDr.Read())
            {
                AL_ColumnNames.Add(OraDr.GetString(0));
            }

            OraDr.Close();
            OraCommand.Dispose();
            OraConnection.Close();

            return AL_ColumnNames;
        }

        protected void RecLastInsDatetime(String AppName, String OrgCode, String FilePathName, String UserName, DateTime InsDatetime)
        {
            //
            //这个方法使用SQLServer，而不是Oracle
            //增加了复杂性，降低了可维护性。
            //最好在Oracle实现。
            //

            //这个也可以用触发器实现，未明确优劣，先这样吧......
            SqlConnection SqlConn = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConn.Open();

            SqlCommand InsertCommand = new SqlCommand("INSERT INTO [dbo].[u_cls_LastInsDatetime] ([ApplicationName],[OrgCode],[FilePathName],[UploadUserName],[LastInsDatetime]) VALUES(@ApplicationName,@OrgCode,@FilePathName,@UploadUserName,@LastInsDatetime)", SqlConn);
            InsertCommand.CommandType = CommandType.Text;

            SqlCommand SelectCommand = new SqlCommand("SELECT TOP(1) LastInsDatetime FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = '" + AppName + "' AND OrgCode='" + OrgCode + "' ORDER BY LastInsDatetime DESC", SqlConn);
            SelectCommand.CommandType = CommandType.Text;

            SqlCommand DeleteCommand = new SqlCommand("DELETE FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = '" + AppName + "' AND OrgCode='" + OrgCode + "'", SqlConn);
            DeleteCommand.CommandType = CommandType.Text;

            SqlDataReader SqlDr = SelectCommand.ExecuteReader();

            if (SqlDr.HasRows)
            {
                //执行DeleteCommand之前，必须首先关闭SqlDataReader，以及SelectCommand
                SqlDr.Close();

                //执行DeleteCommand之前，必须首先关闭SqlDataReader，以及SelectCommand
                SelectCommand.Dispose();

                DeleteCommand.ExecuteNonQuery();
            }

            SqlDr.Close();
            DeleteCommand.Dispose();

            //参考简洁的语法
            //someCommand.Parameters.Add("@username", SqlDbType.NChar).Value = name;
            //http://msdn.microsoft.com/query/dev12.query?appId=Dev12IDEF1&l=ZH-CN&k=k(CA2100);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.0)&rd=true
            SqlParameter[] InsertParameter = new SqlParameter[5];
            InsertParameter[0] = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 50);
            InsertParameter[1] = new SqlParameter("@OrgCode", SqlDbType.NVarChar, 4);
            InsertParameter[2] = new SqlParameter("@FilePathName", SqlDbType.NVarChar, 500);
            InsertParameter[3] = new SqlParameter("@UploadUserName", SqlDbType.NVarChar, 4);
            InsertParameter[4] = new SqlParameter("@LastInsDatetime", SqlDbType.DateTime);

            InsertParameter[0].Value = AppName;
            InsertParameter[1].Value = OrgCode;
            InsertParameter[2].Value = FilePathName;
            InsertParameter[3].Value = UserName;
            InsertParameter[4].Value = InsDatetime;

            for (int i = 0; i < InsertParameter.Length; i++)
            {
                InsertCommand.Parameters.Add(InsertParameter[i]);
            }
            //InsertCommand.Parameters.Add(InsertParameter[1]);
            //InsertCommand.Parameters.Add(InsertParameter[2]);            

            try
            {
                InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                //写入日志
                //WriteLog(e.Message + "||" + e.StackTrace);
            }
            InsertCommand.Dispose();

            SqlConn.Close();
        }

        protected void Button_Test_Click(object sender, EventArgs e)
        {
            Label_DebugMsg.Text = "";

            //GridView_ExcelGridView.DataSource = GetExcelDataSet(null, null);
            //GridView_ExcelGridView.DataBind();

            //String[] SheetNames = ExcelSheetNames(null).ToArray();
            //ArrayList SheetNames = ExcelSheetNames(null);

            //for (int i = 0; i < SheetNames.Count; i++)
            //{
            //    Label_DebugMsg.Text += "SheetNames.Count:" + SheetNames.Count + "<br />";
            //    Label_DebugMsg.Text += "Count:" + i + " - " + SheetNames[i].ToString() + "<br />";
            //    Label_DebugMsg.Text += "<br />";
            //}

            //ExcelImportToSQLDB(GetExcelFilePathName(), AppName);
        }

        protected void Button_Return_Click(object sender, EventArgs e)
        {
            Response.Redirect(ReturnUrl);
        }
    }
}